with first_code as (
    select
      *
    from
      (
        select *,
          row_number() over(partition by code order by flg desc) as rn
        from
          (
               select
                 code  --code
                ,name  --建包名称
                ,type
                ,dest_center_code dest_center_code  --目的中心名称
                ,dest_center_name dest_center_name  --目的中心名称
                ,center_code center_code    --末端机构编码
                ,center_name  center_name   --末端机构名称
                ,2 as flg
                from   jms_dim.dim_lmdm_sys_first_code t
                where
                  t.is_enable = '1' and t.is_delete = '1'
            union all
              select
                 express_center_short_code as code
                ,t.name
                ,type
                ,express_center_code   dest_center_code
                ,t1.name     dest_center_name
                ,consolidation_code    center_code
                ,consolidation_name    center_name
               ,1 as flg
            from jms_dim.dim_yl_sys_package_different_base t
            left join  jms_dim.dim_network_whole_massage t1 on t.express_center_code=t1.code

          ) t
      ) t
    where rn = 1
)

insert overwrite table jms_dm.dm_first_code_optimize partition ( dt )
select 
 to_date(taking_early_scantime) as date_time
,first_code
,max(t1.name) name
,max(t1.type) type
,max(t1.dest_center_code) dest_center_code
,max(t1.dest_center_name) dest_center_name
,max(t1.center_code) center_code
,max(t1.center_name) center_name
,count(1) as first_code_count
,sum(count(1)) over(partition by to_date(taking_early_scantime) ) as taking_date_count
,to_date(taking_early_scantime) as dt
from jms_dwd.dwd_wide_unsign_summary_waybill_dt t 
left join first_code t1 on t.first_code=t1.code
where t.dt>='{{ execution_date | date_add(-6) | cst_ds }}'   and to_date(taking_early_scantime)='{{ execution_date | cst_ds }}'
  and length(first_code)=3
group by 
to_date(taking_early_scantime),
first_code

 distribute by pmod(hash(rand()), 3);
